/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package de.stalinski.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BookingsConnector {

    private static String sUsr = "root";
    private static String sPwd = "admin";
    private static String sDB = "frbrUsers";
    private static String sTable = "bookings";
    private static Connection connection;
    private static String sHost = "localhost";
    private static String sConn = "jdbc:mysql://" + sHost + ":3306/" + sDB;

    public BookingsConnector() throws SQLException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
        }
        try {
            connection = DriverManager.getConnection(sConn, sUsr, sPwd);
            System.out.println(connection != null);
        } catch (SQLException ex) {
        }
    }

    public String showTotalConsumption(String id,String user, String from, String to) throws ClassNotFoundException, SQLException {
        String out = "<table class=\"Prices\">";

        out += "<thead>";
        out += "<th>Datum</th><th>Zeit</th><th>Sorte</th><th>Anzahl</th><th>Preis</th><th>Kosten</th><th>Summe</th>";
        out += "</thead>";
        out += "<tbody>";
        Class.forName("com.mysql.jdbc.Driver");

        connection = DriverManager.getConnection(sConn, sUsr, sPwd);
        Statement st = null;
        ResultSet rs = null;
        st = connection.createStatement();
        rs = st.executeQuery("select * from " + sTable + " WHERE UID='"+id+"' AND USER='" + user + "' AND DATE>='"+from+"' AND DATE<='"+to+"'" +
                "ORDER BY TIME ASC");
        Double total = 0.0;
        while (rs.next()) {
            total += rs.getDouble("VALUE");
            total = Math.round(total * 100.0) / 100.0;
            out += "<tr>";
            out += "<td>" + rs.getString("DATE") + "</td>";
            out += "<td>" + rs.getString("TIME") + "</td>";
            out += "<td>" + rs.getString("SORT") + "</td>";
            out += "<td>" + rs.getString("AMOUNT") + "</td>";
            out += "<td>" + rs.getString("CURRENTPRICE") + "&nbsp;&euro;</td>";
            out += "<td>" + rs.getString("VALUE") + "&nbsp;&euro;</td>";
            out += "<td>" + total + "&nbsp;&euro;</td>";
        }

        connection.close();
        out += "</tbody></table>";
        return out;
    }
}
